package com.icim.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.icim.entity.Ret_cus_prd_indt;
import com.icim.entity.Ret_cus_so;
import com.icim.entity.Ret_prd_info;

@Repository
public class RetCusPrdIndtDao {
    @Resource
    private JdbcTemplate icimJdbcTemplate;

    public void saveBatch(List<Ret_cus_prd_indt> retCusPrdIndtList) {
        List<Object[]> batch = new ArrayList<Object[]>();

        for (Ret_cus_prd_indt retCusIndt : retCusPrdIndtList) {
            batch.add(new Object[] { retCusIndt.getMtrlBoxId(), retCusIndt.getCellBlockId(),
                    retCusIndt.getIsDownGrade(), retCusIndt.getMtrlGrade(), retCusIndt.getMtrlSlotNo(),
                    retCusIndt.getMtrlDefCode(), retCusIndt.getVdrPrdSeqId(), retCusIndt.getMtrlPrdSeqGrd(),
                    retCusIndt.getCusMfgFabId(), retCusIndt.getMtrlLotId(), retCusIndt.getSlimFlg(),
                    retCusIndt.getMtrlPalletNo() });
        }

        StringBuffer sqlBuffer = new StringBuffer("INSERT INTO RET_CUS_PRD_INDT(");
        sqlBuffer.append("MTRL_BOX_ID,CELL_BLOCK_ID,IS_DOWN_GRADE,MTRL_GRADE,MTRL_SLOT_NO,");
        sqlBuffer.append("MTRL_DEF_CODE,VDR_PRD_SEQ_ID,MTRL_PRD_SEQ_GRD,CUS_MFG_FAB_ID,");
        sqlBuffer.append("MTRL_LOT_ID,SLIM_FLG,MTRL_PALLET_NO");
        sqlBuffer.append(") VALUES(");
        sqlBuffer.append("?,?,?,?,?,");
        sqlBuffer.append("?,?,?,?,?,");
        sqlBuffer.append("?,?");
        sqlBuffer.append(")");

        icimJdbcTemplate.batchUpdate(sqlBuffer.toString(), batch);
    };

    public List<Ret_cus_prd_indt> findPrdIndtList(Collection<String> sheetIds) {

        if (sheetIds.size() == 0) {
            new ArrayList<Ret_cus_prd_indt>();
        }

        StringBuffer sqlBuffer = new StringBuffer(
                "SELECT MTRL_BOX_ID,CELL_BLOCK_ID,IS_DOWN_GRADE,MTRL_GRADE,MTRL_SLOT_NO,");
        sqlBuffer.append(" MTRL_DEF_CODE,VDR_PRD_SEQ_ID,MTRL_PRD_SEQ_GRD,CUS_MFG_FAB_ID,");
        sqlBuffer.append(" MTRL_LOT_ID,SLIM_FLG,MTRL_PALLET_NO");
        sqlBuffer.append(" FROM  RET_CUS_PRD_INDT WHERE CELL_BLOCK_ID IN (''");
        Object[] shtIds = sheetIds.toArray();
        for (int i = 0; i < shtIds.length; i++) {
            String sheetId = (String) shtIds[i];
                sqlBuffer.append(",'").append(sheetId).append("'");
        }
        sqlBuffer.append(")");

        return icimJdbcTemplate.query(sqlBuffer.toString(), new RowMapper<Ret_cus_prd_indt>() {

            @Override
            public Ret_cus_prd_indt mapRow(ResultSet rs, int rowNum) throws SQLException {
                Ret_cus_prd_indt retCusPrdIndt = new Ret_cus_prd_indt();
                retCusPrdIndt.setCellBlockId(rs.getString("CELL_BLOCK_ID"));
                retCusPrdIndt.setMtrlPalletNo(rs.getString("MTRL_PALLET_NO"));
                retCusPrdIndt.setMtrlBoxId(rs.getString("MTRL_BOX_ID"));
                retCusPrdIndt.setSlimFlg(rs.getString("SLIM_FLG"));
                return retCusPrdIndt;
            }
        });
    }
}
